<?php
use Migrations\AbstractMigration;

class POCOR7230 extends AbstractMigration
{
    /**
     * Change Method.
     *
     * More information on this method is available here:
     * http://docs.phinx.org/en/latest/migrations.html#the-change-method
     * @return void
     */
    public function up()
    {
   
        /** Delete existing Core procedure */
        $this->execute('DROP PROCEDURE IF EXISTS `openemis_core_reports`');

        /** Create new OpenEMIS Core procedure that now does not stop each time when error occurs, but rather ignores the error and proceedes with another query */
        $this->execute('CREATE PROCEDURE `openemis_core_reports`(IN `var_interval` VARCHAR(10)) NO SQL BEGIN DECLARE var_row TEXT; DECLARE var_done INT DEFAULT FALSE; DECLARE var_cursor CURSOR FOR SELECT query_sql FROM report_queries WHERE status = 1 AND frequency LIKE var_interval ORDER BY id ASC; DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done = TRUE; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET var_done = FALSE; OPEN var_cursor; read_loop: LOOP FETCH var_cursor INTO var_row; IF var_done THEN LEAVE read_loop; END IF; SET @var_row = var_row; PREPARE report_query FROM @var_row; EXECUTE report_query; DEALLOCATE PREPARE report_query; END LOOP; CLOSE var_cursor; END ;');

        // Backup table
        $this->execute('CREATE TABLE `zz_7230_report_queries` LIKE `report_queries`');
        $this->execute('INSERT INTO `zz_7230_report_queries` SELECT * FROM `report_queries`');

        // DROP existing summary tables
        $this->execute('DROP TABLE summary_institution_nationalities;');
        $this->execute('DROP TABLE summary_institution_grade_nationalities;');
        
        // DELETE existing queries related to nationalities to ensure the the new inserts are in the right order
        $this->execute('DELETE FROM report_queries WHERE report_queries.name LIKE "%nationalities%";');
        

        // ADD new CREATE statements 
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_institution_nationalities_create", "CREATE TABLE IF NOT EXISTS `summary_institution_nationalities`( `academic_period_id` int(11) NOT NULL, `academic_period_name` varchar(50) NOT NULL, `institution_id` int(11) NOT NULL, `institution_code` varchar(50) NOT NULL, `nationality_id` int(11) NULL, `nationality_name` varchar(50) NULL, `total_students` int(9) NOT NULL, `total_students_female` int(9) NOT NULL, `total_students_male` int(9) NOT NULL) ENGINE = InnoDB DEFAULT CHARSET = utf8;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_institution_nationalities_truncate", "TRUNCATE summary_institution_nationalities;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_institution_nationalities_insert", "INSERT INTO summary_institution_nationalities (academic_period_id, academic_period_name, institution_id, institution_code, nationality_id, nationality_name, total_students, total_students_female, total_students_male) SELECT academic_periods.id academic_period_id ,academic_periods.name academic_period_name ,institutions.id institution_id ,institutions.code institution_code ,IFNULL(nationalities.id, 0) nationality_id ,nationalities.name nationality_name ,SUM(CASE WHEN security_users.gender_id IN (1,2) AND IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), institution_students.student_status_id = 1, institution_students.student_status_id IN (1, 7, 6, 8)) THEN 1 ELSE 0 END) total_students ,SUM(CASE WHEN security_users.gender_id = 2 AND IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), institution_students.student_status_id = 1, institution_students.student_status_id IN (1, 7, 6, 8)) THEN 1 ELSE 0 END) total_students_female ,SUM(CASE WHEN security_users.gender_id = 1 AND IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), institution_students.student_status_id = 1, institution_students.student_status_id IN (1, 7, 6, 8)) THEN 1 ELSE 0 END) total_students_male FROM institution_students INNER JOIN security_users ON security_users.id = institution_students.student_id INNER JOIN institutions ON institutions.id = institution_students.institution_id INNER JOIN academic_periods ON academic_periods.id = institution_students.academic_period_id LEFT JOIN user_nationalities ON user_nationalities.security_user_id = security_users.id LEFT JOIN nationalities ON nationalities.id = user_nationalities.nationality_id GROUP BY institution_students.academic_period_id ,institution_students.institution_id ,nationalities.id;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_institution_grade_nationalities_create", "CREATE TABLE IF NOT EXISTS `summary_institution_grade_nationalities`( `academic_period_id` int(11) NOT NULL, `academic_period_name` varchar(50) NOT NULL, `institution_id` int(11) NOT NULL, `institution_code` varchar(50) NOT NULL, `grade_id` int(11) NOT NULL, `grade_name` varchar(50) NOT NULL, `nationality_id` int(11) NULL, `nationality_name` varchar(50) NULL, `total_students` int(9) NOT NULL, `total_students_female` int(9) NOT NULL, `total_students_male` int(9) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_institution_grade_nationalities_truncate", "TRUNCATE summary_institution_grade_nationalities;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        $this->execute('INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ("summary_institution_grade_nationalities_insert", "INSERT INTO summary_institution_grade_nationalities(academic_period_id, academic_period_name, institution_id, institution_code, grade_id, grade_name, nationality_id, nationality_name, total_students, total_students_female, total_students_male) SELECT academic_periods.id academic_period_id ,academic_periods.name academic_period_name ,institutions.id institution_id ,institutions.code institution_code ,education_grades.id grade_id ,education_grades.name grade_name ,IFNULL(nationalities.id, 0) nationality_id ,nationalities.name nationality_name ,SUM(CASE WHEN security_users.gender_id IN (1,2) AND IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), institution_students.student_status_id = 1, institution_students.student_status_id IN (1, 7, 6, 8)) THEN 1 ELSE 0 END) total_students ,SUM(CASE WHEN security_users.gender_id = 2 AND IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), institution_students.student_status_id = 1, institution_students.student_status_id IN (1, 7, 6, 8)) THEN 1 ELSE 0 END) total_students_female ,SUM(CASE WHEN security_users.gender_id = 1 AND IF((CURRENT_DATE >= academic_periods.start_date AND CURRENT_DATE <= academic_periods.end_date), institution_students.student_status_id = 1, institution_students.student_status_id IN (1, 7, 6, 8)) THEN 1 ELSE 0 END) total_students_male FROM institution_students INNER JOIN security_users ON security_users.id = institution_students.student_id INNER JOIN education_grades ON education_grades.id = institution_students.education_grade_id INNER JOIN institutions ON institutions.id = institution_students.institution_id INNER JOIN academic_periods ON academic_periods.id = institution_students.academic_period_id LEFT JOIN user_nationalities ON user_nationalities.security_user_id = security_users.id LEFT JOIN nationalities ON nationalities.id = user_nationalities.nationality_id GROUP BY institution_students.academic_period_id ,institution_students.institution_id ,education_grades.id ,nationalities.id;", "week", 1, NULL, NULL, 1, CURRENT_TIMESTAMP)');
        
        $this->execute("INSERT INTO `report_queries` (`name`, `query_sql`, `frequency`, `status`, `modified_user_id`, `modified`, `created_user_id`, `created`) VALUES ('summary_isced_sectors_insert', 'INSERT INTO summary_isced_sectors(academic_period_id, academic_period_name, institution_sector_id, institution_sector_name, education_system_id, education_system_name, education_level_isced_id, education_level_isced_name, education_level_isced_level, total_instiutions, total_electricity_institutions, total_computer_institutions, total_teaching_computer_institutions, total_internet_institutions, total_toilet_institutions, total_improved_toilet_institutions, total_single_sex_toilet_institutions, total_improved_single_sex_toilet_institutions, total_in_use_toilet_institutions, total_in_use_improved_toilet_institutions, total_in_use_single_sex_toilet_institutions, total_improved_in_use_single_sex_toilet_institutions, total_drinking_water_institutions, total_functional_drinking_water_institutions, total_handwashing_facility_institutions, total_accessible_room_institutions) SELECT main_query.academic_period_id ,main_query.academic_period_name ,main_query.institution_sector_id ,main_query.institution_sector_name ,main_query.education_system_id ,main_query.education_system_name ,main_query.education_level_isced_id ,main_query.education_level_isced_name ,main_query.education_level_isced_level ,COUNT(DISTINCT(main_query.institution_id)) count_total_instiutions ,IFNULL(COUNT(DISTINCT(electricity_info.institution_id)),0) count_electricity_institutions ,IFNULL(COUNT(DISTINCT(computer_info.institution_id)),0) count_computer_institutions ,IFNULL(COUNT(DISTINCT(teaching_computer_info.institution_id)),0) count_teaching_computer_institutions ,IFNULL(COUNT(DISTINCT(internet_info.institution_id)),0) count_internet_institutions ,IFNULL(COUNT(DISTINCT(toilet_info.institution_id)),0) count_toilet_institutions ,IFNULL(COUNT(DISTINCT(improved_toilet_info.institution_id)),0) count_improved_toilet_institutions ,IFNULL(COUNT(DISTINCT(single_sex_toilet_info.institution_id)),0) count_single_sex_toilet_institutions ,IFNULL(COUNT(DISTINCT(single_sex_improved_toilet_info.institution_id)),0) count_improved_single_sex_toilet_institutions ,IFNULL(COUNT(DISTINCT(in_use_toilet_info.institution_id)),0) count_in_use_toilet_institutions ,IFNULL(COUNT(DISTINCT(in_use_improved_toilet_info.institution_id)),0) count_in_use_improved_toilet_institutions ,IFNULL(COUNT(DISTINCT(in_use_single_sex_toilet_info.institution_id)),0) count_in_use_single_sex_toilet_institutions ,IFNULL(COUNT(DISTINCT(improved_in_use_single_sex_toilet_info.institution_id)),0) count_improved_in_use_single_sex_toilet_institutions ,IFNULL(COUNT(DISTINCT(drinking_water_info.institution_id)),0) count_drinking_water_institutions ,IFNULL(COUNT(DISTINCT(available_drinking_water_info.institution_id)),0) count_functional_drinking_water_institutions ,IFNULL(COUNT(DISTINCT(handwashing_facility_info.institution_id)),0) count_handwashing_facility_institutions ,IFNULL(COUNT(DISTINCT(accessible_rooms_info.institution_id)),0) count_accessible_room_institutions FROM ( SELECT academic_periods.id academic_period_id ,academic_periods.name academic_period_name ,institution_sectors.id institution_sector_id ,institution_sectors.name institution_sector_name ,education_systems.id education_system_id ,education_systems.name education_system_name ,education_level_isced.id education_level_isced_id ,education_level_isced.name education_level_isced_name ,education_level_isced.isced_level education_level_isced_level ,institutions.id institution_id FROM institution_grades INNER JOIN institutions ON institutions.id = institution_grades.institution_id INNER JOIN institution_sectors ON institution_sectors.id = institutions.institution_sector_id INNER JOIN education_grades ON education_grades.id = institution_grades.education_grade_id INNER JOIN education_programmes ON education_programmes.id = education_grades.education_programme_id INNER JOIN education_cycles ON education_cycles.id = education_programmes.education_cycle_id INNER JOIN education_levels ON education_levels.id = education_cycles.education_level_id INNER JOIN education_level_isced ON education_level_isced.id = education_levels.education_level_isced_id INNER JOIN education_systems ON education_systems.id = education_levels.education_system_id INNER JOIN academic_periods ON academic_periods.id = education_systems.academic_period_id INNER JOIN ( SELECT academic_periods.id academic_period_id ,academic_periods.name academic_period_name ,institution_sectors.id institution_sector_id ,institution_sectors.name institution_sector_name ,education_systems.id education_system_id ,education_systems.name education_system_name ,education_level_isced.id education_level_isced_id ,education_level_isced.name education_level_isced_name ,MAX(education_level_isced.isced_level) education_level_isced_level ,institutions.id institution_id FROM institution_grades INNER JOIN institutions ON institutions.id = institution_grades.institution_id INNER JOIN institution_sectors ON institution_sectors.id = institutions.institution_sector_id INNER JOIN education_grades ON education_grades.id = institution_grades.education_grade_id INNER JOIN education_programmes ON education_programmes.id = education_grades.education_programme_id INNER JOIN education_cycles ON education_cycles.id = education_programmes.education_cycle_id INNER JOIN education_levels ON education_levels.id = education_cycles.education_level_id INNER JOIN education_level_isced ON education_level_isced.id = education_levels.education_level_isced_id INNER JOIN education_systems ON education_systems.id = education_levels.education_system_id INNER JOIN academic_periods ON academic_periods.id = education_systems.academic_period_id WHERE institutions.institution_status_id = 1 GROUP BY academic_periods.id ,institution_sectors.id ,institutions.id) max_isced_level ON max_isced_level.academic_period_id = academic_periods.id AND max_isced_level.institution_sector_id = institution_sectors.id AND max_isced_level.institution_id = institutions.id AND max_isced_level.education_level_isced_level = education_level_isced.isced_level WHERE institutions.institution_status_id = 1 GROUP BY academic_periods.id ,institution_sectors.id ,education_level_isced.id ,institutions.id ) main_query LEFT JOIN ( SELECT infrastructure_utility_electricities.academic_period_id ,infrastructure_utility_electricities.institution_id FROM infrastructure_utility_electricities GROUP BY infrastructure_utility_electricities.academic_period_id ,infrastructure_utility_electricities.institution_id ) electricity_info ON electricity_info.academic_period_id = main_query.academic_period_id AND electricity_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT institution_assets.academic_period_id ,institution_assets.institution_id FROM institution_assets INNER JOIN asset_types ON asset_types.id = institution_assets.asset_type_id WHERE institution_assets.asset_status_id = 1 AND asset_types.name LIKE \"%Computer%\" OR asset_types.name LIKE \"%Laptop%\" GROUP BY institution_assets.academic_period_id ,institution_assets.institution_id ) computer_info ON computer_info.academic_period_id = main_query.academic_period_id AND computer_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT institution_assets.academic_period_id ,institution_assets.institution_id FROM institution_assets INNER JOIN asset_types ON asset_types.id = institution_assets.asset_type_id WHERE institution_assets.purpose = 1 AND institution_assets.asset_status_id = 1 AND asset_types.name LIKE \"%Computer%\" OR asset_types.name LIKE \"%Laptop%\" GROUP BY institution_assets.academic_period_id ,institution_assets.institution_id ) teaching_computer_info ON teaching_computer_info.academic_period_id = main_query.academic_period_id AND teaching_computer_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_utility_internets.academic_period_id ,infrastructure_utility_internets.institution_id FROM infrastructure_utility_internets GROUP BY infrastructure_utility_internets.academic_period_id ,infrastructure_utility_internets.institution_id ) internet_info ON internet_info.academic_period_id = main_query.academic_period_id AND internet_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id FROM infrastructure_wash_sanitations GROUP BY infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id ) toilet_info ON toilet_info.academic_period_id = main_query.academic_period_id AND toilet_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id FROM infrastructure_wash_sanitations INNER JOIN infrastructure_wash_sanitation_qualities ON infrastructure_wash_sanitation_qualities.id = infrastructure_wash_sanitations.infrastructure_wash_sanitation_quality_id WHERE infrastructure_wash_sanitation_qualities.name LIKE \"%Improved%\" GROUP BY infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id ) improved_toilet_info ON improved_toilet_info.academic_period_id = main_query.academic_period_id AND improved_toilet_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id FROM infrastructure_wash_sanitations GROUP BY infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id HAVING ( SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_male) = 0 AND SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_mixed) = 0 ) OR ( SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_female) = 0 AND SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_mixed) = 0 ) ) single_sex_toilet_info ON single_sex_toilet_info.academic_period_id = main_query.academic_period_id AND single_sex_toilet_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id FROM infrastructure_wash_sanitations INNER JOIN infrastructure_wash_sanitation_qualities ON infrastructure_wash_sanitation_qualities.id = infrastructure_wash_sanitations.infrastructure_wash_sanitation_quality_id WHERE infrastructure_wash_sanitation_qualities.name LIKE \"%Improved%\" GROUP BY infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id HAVING ( SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_male) = 0 AND SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_mixed) = 0 ) OR ( SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_female) = 0 AND SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_mixed) = 0 ) ) single_sex_improved_toilet_info ON single_sex_improved_toilet_info.academic_period_id = main_query.academic_period_id AND single_sex_improved_toilet_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id FROM infrastructure_wash_sanitations INNER JOIN ( SELECT * FROM infrastructure_wash_sanitation_quantities WHERE infrastructure_wash_sanitation_quantities.functional = 1 GROUP BY infrastructure_wash_sanitation_quantities.infrastructure_wash_sanitation_id ,infrastructure_wash_sanitation_quantities.functional HAVING SUM(infrastructure_wash_sanitation_quantities.value) > 0 ) in_use_toilets ON in_use_toilets.infrastructure_wash_sanitation_id = infrastructure_wash_sanitations.id GROUP BY infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id ) in_use_toilet_info ON in_use_toilet_info.academic_period_id = main_query.academic_period_id AND in_use_toilet_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id FROM infrastructure_wash_sanitations INNER JOIN ( SELECT * FROM infrastructure_wash_sanitation_quantities WHERE infrastructure_wash_sanitation_quantities.functional = 1 GROUP BY infrastructure_wash_sanitation_quantities.infrastructure_wash_sanitation_id ,infrastructure_wash_sanitation_quantities.functional HAVING SUM(infrastructure_wash_sanitation_quantities.value) > 0 ) in_use_toilets ON in_use_toilets.infrastructure_wash_sanitation_id = infrastructure_wash_sanitations.id INNER JOIN infrastructure_wash_sanitation_qualities ON infrastructure_wash_sanitation_qualities.id = infrastructure_wash_sanitations.infrastructure_wash_sanitation_quality_id WHERE infrastructure_wash_sanitation_qualities.name LIKE \"%Improved%\" GROUP BY infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id ) in_use_improved_toilet_info ON in_use_improved_toilet_info.academic_period_id = main_query.academic_period_id AND in_use_improved_toilet_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id FROM infrastructure_wash_sanitations INNER JOIN ( SELECT * FROM infrastructure_wash_sanitation_quantities WHERE infrastructure_wash_sanitation_quantities.functional = 1 GROUP BY infrastructure_wash_sanitation_quantities.infrastructure_wash_sanitation_id ,infrastructure_wash_sanitation_quantities.functional HAVING SUM(infrastructure_wash_sanitation_quantities.value) > 0 ) in_use_toilets ON in_use_toilets.infrastructure_wash_sanitation_id = infrastructure_wash_sanitations.id GROUP BY infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id HAVING ( SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_male) = 0 AND SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_mixed) = 0 ) OR ( SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_female) = 0 AND SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_mixed) = 0 ) ) in_use_single_sex_toilet_info ON in_use_single_sex_toilet_info.academic_period_id = main_query.academic_period_id AND in_use_single_sex_toilet_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id FROM infrastructure_wash_sanitations INNER JOIN ( SELECT * FROM infrastructure_wash_sanitation_quantities WHERE infrastructure_wash_sanitation_quantities.functional = 1 GROUP BY infrastructure_wash_sanitation_quantities.infrastructure_wash_sanitation_id ,infrastructure_wash_sanitation_quantities.functional HAVING SUM(infrastructure_wash_sanitation_quantities.value) > 0 ) in_use_toilets ON in_use_toilets.infrastructure_wash_sanitation_id = infrastructure_wash_sanitations.id INNER JOIN infrastructure_wash_sanitation_qualities ON infrastructure_wash_sanitation_qualities.id = infrastructure_wash_sanitations.infrastructure_wash_sanitation_quality_id WHERE infrastructure_wash_sanitation_qualities.name LIKE \"%Improved%\" GROUP BY infrastructure_wash_sanitations.academic_period_id ,infrastructure_wash_sanitations.institution_id HAVING ( SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_male) = 0 AND SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_mixed) = 0 ) OR ( SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_female) = 0 AND SUM(infrastructure_wash_sanitations.infrastructure_wash_sanitation_total_mixed) = 0 ) ) improved_in_use_single_sex_toilet_info ON improved_in_use_single_sex_toilet_info.academic_period_id = main_query.academic_period_id AND improved_in_use_single_sex_toilet_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_waters.academic_period_id ,infrastructure_wash_waters.institution_id FROM infrastructure_wash_waters GROUP BY infrastructure_wash_waters.academic_period_id ,infrastructure_wash_waters.institution_id ) drinking_water_info ON drinking_water_info.academic_period_id = main_query.academic_period_id AND drinking_water_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_waters.academic_period_id ,infrastructure_wash_waters.institution_id FROM infrastructure_wash_waters INNER JOIN infrastructure_wash_water_functionalities ON infrastructure_wash_water_functionalities.id = infrastructure_wash_waters.infrastructure_wash_water_functionality_id WHERE infrastructure_wash_water_functionalities.name NOT LIKE \"%Not Functional%\" GROUP BY infrastructure_wash_waters.academic_period_id ,infrastructure_wash_waters.institution_id ) available_drinking_water_info ON available_drinking_water_info.academic_period_id = main_query.academic_period_id AND available_drinking_water_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT infrastructure_wash_hygienes.academic_period_id ,infrastructure_wash_hygienes.institution_id FROM infrastructure_wash_hygienes INNER JOIN infrastructure_wash_hygiene_types ON infrastructure_wash_hygiene_types.id = infrastructure_wash_hygienes.infrastructure_wash_hygiene_type_id WHERE infrastructure_wash_hygiene_types.name NOT LIKE \"%Not Available%\" GROUP BY infrastructure_wash_hygienes.academic_period_id ,infrastructure_wash_hygienes.institution_id ) handwashing_facility_info ON handwashing_facility_info.academic_period_id = main_query.academic_period_id AND handwashing_facility_info.institution_id = main_query.institution_id LEFT JOIN ( SELECT institution_rooms.academic_period_id ,institution_rooms.institution_id FROM institution_rooms WHERE institution_rooms.accessibility = 1 GROUP BY institution_rooms.academic_period_id ,institution_rooms.institution_id ) accessible_rooms_info ON accessible_rooms_info.academic_period_id = main_query.academic_period_id AND accessible_rooms_info.institution_id = main_query.institution_id GROUP BY main_query.academic_period_id ,main_query.institution_sector_id ,main_query.education_level_isced_id;', 'week', 1, NULL, NULL, 1, CURRENT_TIMESTAMP)");
     
    }

    //rollback
    public function down()
    {
        
        /** Delete latest OpenEMIS Core procedure */
        $this->execute('DROP PROCEDURE IF EXISTS `openemis_core_reports`');

        /** Restaure the procedure that was used before */
        $this->execute('CREATE PROCEDURE `openemis_core_reports`(IN `var_interval` VARCHAR(10)) NO SQL BEGIN  DECLARE var_row TEXT; DECLARE var_done INT DEFAULT FALSE; DECLARE var_cursor CURSOR FOR SELECT query_sql FROM report_queries WHERE status = 1 AND frequency LIKE var_interval ORDER BY id ASC; DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done = TRUE; OPEN var_cursor; read_loop: LOOP FETCH var_cursor INTO var_row; IF var_done THEN LEAVE read_loop; END IF; SET @var_row = var_row; PREPARE report_query FROM @var_row;  EXECUTE report_query;  DEALLOCATE PREPARE report_query; END LOOP; CLOSE var_cursor; END ;');

        // Restore table
        $this->execute('DROP TABLE IF EXISTS `report_queries`');
        $this->execute('RENAME TABLE `zz_7230_report_queries` TO `report_queries`');

        // Drop summary tables
        $this->execute('DROP TABLE IF EXISTS `summary_institution_nationalities`');
        $this->execute('DROP TABLE IF EXISTS `summary_institution_grade_nationalities`');
    }
}